 1.找出全部夺得3连贯的队伍
-- 连续值的求解
-- 1.使用row_number给数据编号
SELECT team, year,
       row_number() OVER (PARTITION BY team ORDER BY year) num
  from t1   
   
-- 2. 某个值 - rownum = num，得到结果可以作为后面分组计算的依据
SELECT team,
      (year - row_number() OVER (PARTITION BY team ORDER BY year)) diff
  from t1
-- 3. 1和2合并求出结果
with tmp as (
SELECT team,
      (year - row_number() OVER (PARTITION BY team ORDER BY year)) diff
  from t1)
select team, diff
  from tmp
group by team, diff
having count(*)=3

 2.找出每个id在在一天之内所有的波峰与波谷值
   
--1. 计算当前行与上一行的结果差和当前行与下一行的结果差
SELECT id, `time`, price,
      lag(price,1,price) OVER(PARTITION BY id ORDER BY `time`) lag1,
      lead(price,1,price) OVER(PARTITION BY id ORDER BY `time`) lead1
   FROM t2
-- 2. 根据条件来判断波峰和波谷
WITH tmp as (
SELECT id, `time`, price,
      lag(price,1,price) OVER(PARTITION BY id ORDER BY `time`) lag1,
      lead(price,1,price) OVER(PARTITION BY id ORDER BY `time`) lead1
   FROM t2)
SELECT id, `time`, price,
      CASE when price>lag1 and price>lead1 then "bofeng"
           when price<lag1 and price<lead1 then "bogu"
      end feature
   FROM tmp
where (price>lag1 and price>lead1) or (price<lag1 and price<lead1)   

 3.写sql
   
   3.1 每个id浏览时长、步长
--1 时长是由最大时间与最小时间的差，可以通过max()和min()聚合函数得到
SELECT id,
   unix_timestamp(max(dt),"yyyy/MM/dd HH:mm") max_minute,
   unix_timestamp(min(dt),"yyyy/MM/dd HH:mm") min_minute
  from t3
GROUP BY id
-- 2. 每个id浏览时长、步长  
   SELECT id, 
      (unix_timestamp(max(dt),"yyyy/MM/dd HH:mm")
      -unix_timestamp(min(dt),"yyyy/MM/dd HH:mm"))/60 minute_diff,
count(*) num
from t3
GROUP BY id  